#encoding=utf-8
# 以需要的时间间隔执行某个命令

import time, os,datetime,traceback,MySQLdb

#from  andy_manager import settings
DATABASES = {
    'default00': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'game_manager',                      # Or path to database file if using sqlite3.
        # The following settings are not used with sqlite3:
        'USER': 'root',
        'PASSWORD': 'hagoot3008#))*',
        'HOST': '127.0.0.1',                      # Empty for localhost through domain sockets or '127.0.0.1' for localhost through TCP.
        'PORT': 3306,                      # Set to empty string for default.
       # 'OPTIONS': {'charset':'utf8mb4'},
    },
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'game_manager',                      # Or path to database file if using sqlite3.
        # The following settings are not used with sqlite3:
        'USER': 'root',
        'PASSWORD': 'mjgame@2017',
        'HOST': '10.66.242.200',                      # Empty for localhost through domain sockets or '127.0.0.1' for localhost through TCP.
        'PORT': 4300,                      # Set to empty string for default.
       # 'OPTIONS': {'charset':'utf8mb4'},
    }
}

def do_sql_view(sql,isRes=0):
    #from django.db import connection, transaction
    conn=None
    res=0
    try:
        #cursor = connection.cursor()
        db_config=DATABASES['default']
        conn = MySQLdb.connect(user=db_config['USER'], db=db_config['NAME'], passwd=db_config['PASSWORD'], host=db_config['HOST'],port=db_config['PORT'])#, charset='utf8'
        cursor = conn.cursor()

        # Data modifying operation
        cursor.execute(sql)
        if isRes:
            res= list(cursor.fetchall())
        else:
            res=1
        # Since we modified data, mark the transaction as dirty
        #transaction.set_dirty()
        #transaction.commit_unless_managed()
        cursor.close()

        #提交
        conn.commit()
        #关闭
        conn.close()
    except:
        print(traceback.format_exc())
        if conn:
            conn.close()
    return res

#统计开桌数据
def count_table(hour,minute):
    #开桌汇总,之前的有没有汇总
            sql="select count(id) as num from websys_tablereport"
            arr=do_sql_view(sql,1)
            #print arr
            if arr and arr[0][0]==0:
                sql='insert into websys_tablereport (gameid,ucount,tcount,tdate)'
                sql+=" SELECT gameid_id,COUNT(DISTINCT uid_id) AS ucount,COUNT(id) AS num,DATE_FORMAT(create_time,'%Y-%m-%d') AS pdate FROM websys_tableinfo WHERE game_time IS not NULL GROUP BY gameid_id,DATE_FORMAT(create_time,'%Y-%m-%d')"
                print '---count_table--init-->',do_sql_view(sql)
            else:
                def do_one_date(sql):
                    db_arr=do_sql_view(sql,1)
                    for ii_data in db_arr:#每个游戏遍历
                        gid=ii_data[0]
                        sql="select id from websys_tablereport where tdate='"+t_date+"' and gameid="+str(gid)
                        cur_arr=do_sql_view(sql,1)
                        if cur_arr:
                            sql="update websys_tablereport set ucount=%s,tcount=%s where id=%s"%(ii_data[1],ii_data[2],cur_arr[0][0])
                        else:
                            sql="insert into websys_tablereport (gameid,ucount,tcount,tdate) values (%s,%s,%s,'%s')" %(ii_data[0],ii_data[1],ii_data[2],t_date)
                        print t_date,gid,'--gid--',do_sql_view(sql)

                cur_date=time.strftime('%Y-%m-%d',time.localtime(time.time()))
                if hour==0 and minute<10:#昨天的重新统计次
                    print 'yestoday----'
                    now=datetime.datetime.now()
                    delta=datetime.timedelta(days=-1)
                    n_days=now+delta
                    t_date=datetime.datetime.strftime(n_days,'%Y-%m-%d')
                    sql="SELECT gameid_id,COUNT(DISTINCT uid_id) AS ucount,COUNT(id) AS num FROM websys_tableinfo WHERE game_time IS not NULL AND create_time>='"+t_date+"' and create_time<'"+cur_date+"' GROUP BY gameid_id"
                    do_one_date(sql)

                #更新当天的
                t_date=cur_date
                #获取已已开的
                sql="SELECT gameid_id,COUNT(DISTINCT uid_id) AS ucount,COUNT(id) AS num FROM websys_tableinfo WHERE game_time IS not NULL AND create_time>'"+t_date+"' GROUP BY gameid_id"
                do_one_date(sql)
                # db_arr=do_sql_view(sql,1)
                # for ii_data in db_arr:#每个游戏遍历
                #     gid=ii_data[0]
                #     sql="select id from websys_tablereport where tdate='"+t_date+"' and gameid="+str(gid)
                #     cur_arr=do_sql_view(sql,1)
                #     if cur_arr:
                #         sql="update websys_tablereport set ucount=%s,tcount=%s where id=%s"%(db_arr[0][1],db_arr[0][2],db_arr[0][0])
                #     else:
                #         sql="insert into websys_tablereport (gameid,ucount,tcount,tdate) values (db_arr[0][0],db_arr[0][1],db_arr[0][2],t_date)"
                #     print gid,'--gid--',do_sql_view(sql)

#统计代理报表http://127.0.0.1:8080/api/recharge/com
#请求远程数据
API_URL='http://agent.game456789.com/api/recharge/com'
import urllib2,ujson,random
def getHttpData(_parms):
    try:
        turl=API_URL+str(_parms)

        req = urllib2.Request(turl)
            #print req

        res_data = urllib2.urlopen(req)
        res = res_data.read()
        return  ujson.loads(res)
    except Exception,e:
        print (datetime.datetime.now(),'--getHttpData-err--',turl)
        #traceback.print_exc()
        return {'ret':-1}
def count_agent_report():
    print getHttpData('?'+str(random.randint(0,100)))

global DO_HOUR,DO_STATUS
DO_HOUR=4#执行的时间
DO_STATUS=0#执行的状态
def re_exe(cmd, inc = 60):
    while True:
        #当前小时
        t_hour=int(datetime.datetime.strftime(datetime.datetime.now(),'%H'))
        t_min=int(datetime.datetime.strftime(datetime.datetime.now(),'%M'))
        global DO_HOUR,DO_STATUS
        if t_hour==DO_HOUR and t_min<20:
            if DO_STATUS==0:#执行
                #os.system(cmd);
                print(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))
                t_parm='FROM websys_gamerecord WHERE start_time<DATE_ADD(NOW(),INTERVAL -8 DAY)'
                #sql='insert into websys_gamerecord_copy select * '+t_parm+';'
                t_date='<DATE_ADD(NOW(),INTERVAL -8 DAY);'
                #记录
                sql='delete FROM websys_gamerecord WHERE start_time'+t_date
                #预扣表
                sql+='delete from websys_withholdinfo where create_time'+t_date
                #桌子表
                sql+='delete from websys_tableinfo where round=0 and game_time IS NULL and create_time'+t_date
                print(sql,'--do_sql_view---',do_sql_view(sql))

                #count_agent_report()
                DO_STATUS=1
        elif DO_STATUS==1:
            DO_STATUS=0
        else:
            print(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))
            count_table(t_hour,t_min)

        time.sleep(inc)


re_exe("echo %time%", 60*5)
#do_sql_view('select * from websys_userinfo')
#count_agent_report()
#count_table(11,11)